![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Be careful not to increase the amount of memory you require beyond that set aside by the operating system. Any paging or swapping caused by over-allocating memory will offset any advantage you get from the library cache. If you have plenty of memory, you may be able to speed access to the shared SQL areas by setting the Oracle initialization parameter CURSOR_SPACE_FOR_TIME equal to TRUE. When this parameter is TRUE, it specifies that a shared SQL area cannot be deallocated until all the cursors associated with it are closed. If CURSOR_SPACE_FOR_TIME is TRUE, it is not necessary for Oracle to check whether the SQL statement is in the library cache because this parsed statement cannot be deallocated as long as the cursor is open. If memory is scarce on your system, do not set this parameter. If the value is TRUE and there is no space in the shared pool for a new SQL statement, an error will be returned, thus halting the application. Data Dictionary Cache The data dictionary contains a set of tables and views Oracle uses as a reference to the database. Oracle stores information here about both the logical and physical structure of the database. The data dictionary contains information such as the following:
The data dictionary is frequently accessed by Oracle itself for the parsing of SQL statements. This access is essential to the operation of Oracle; performance bottlenecks in the data dictionary affect all Oracle users. You can check the efficiency of the data dictionary cache. Statistics for the data dictionary cache are stored in the dynamic performance table V$ROWCACHE (the data dictionary cache is sometimes known as the row cache). The important columns to view in this table are GETS and GETMISSES:
A few number of cache misses are expected, especially during startup when the cache has not been populated. To get an idea of the total number of cache misses, use the following statement: SQL> SELECT SUM(getmisses) "Cache Misses", 2 SUM(gets) "Requests", 3 100 * ( SUM(getmisses) / SUM(gets) ) "Cache Miss Percent" 4 FROM v$rowcache; Cache Misses Requests Cache Miss Percent ------------ -------- ---------- ------- 277 2185 12.677346 The output shown here indicates that a sum of 2,185 requests were made to the data dictionary cache with 277 data dictionary cache misses. This means that 12.68 percent of these requests caused a cache miss. To get the cache miss statistics on the individual elements of the data dictionary, execute this SQL statement: SQL> SELECT parameter, 2 getmisses "Cache Misses", 3 gets "Requests" 4 FROM v$rowcache; PARAMETER Cache Misses Requests -------------------------------- ------------ -------- dc_free_extents 4 92 dc_used_extents 0 0 dc_segments 1 7 dc_tablespaces 0 0 dc_tablespaces 0 0 dc_tablespace_quotas 0 0 dc_files 0 0 dc_users 8 90 dc_rollback_segments 4 122 dc_objects 44 378 dc_constraints 0 0 dc_object_ids 0 0 dc_tables 20 318 dc_synonyms 5 9 dc_sequences 1 6 dc_usernames 4 71 dc_database_links 0 0 dc_histogram_defs 0 0 dc_profiles 0 0 dc_users 0 0 dc_columns 153 1028 PARAMETER Cache Misses Requests -------------------------------- ------------ -------- dc_table_grants 14 18 dc_column_grants 0 0 dc_indexes 12 176 dc_constraint_defs 7 7 dc_constraint_defs 0 0 dc_sequence_grants 0 0 dc_user_grants 7 62 28 rows selected. In frequently accessed dictionary caches, the miss rate should not rise above 10 to 15 percent. If the percent of misses continues to increase during run time, increase the amount of memory allocated for the data dictionary cache. Use the same parameter as for the library cache: SHARED_POOL_SIZE.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement. |